Show how a modern web app performs CRUD operations securely using:
Your student CRUD app manages students in a students table.
1️⃣ UI (React / HTML)
- User clicks Create / Read / Update / Delete
- Sends HTTPS request to EC2 API (/api/v1/students)
2️⃣ API (Node.js / Express on EC2)
- Receives JSON payload from UI
- Needs DB credentials → calls Secrets Manager
- Uses IAM role instead of hard-coded username/password
3️⃣ AWS Secrets Manager (encrypted with KMS)
- Stores database username, password, host, dbname
- Only API's IAM role has permission to GetSecretValue
- KMS transparently decrypts secret for authorized callers
4️⃣ Amazon RDS (MySQL/PostgreSQL)
- API opens connection using credentials from Secrets Manager
- Executes INSERT / SELECT / UPDATE / DELETE
- Returns rows or status back to API
5️⃣ UI Update
- API returns JSON response
- UI refreshes table with latest data
Bonus:
- Read-only SELECT queries can be routed to a Read Replica.
- In case of replica issues, API can fail over back to primary.
- Replication lag explains why GET-after-POST may show stale data.API Version: v1
All endpoints are prefixed with /api/v1 and return JSON.
Base URL (example)
https://student-api.ideafarm-sp.edu/api/v1GET /students
POST /students
PUT /students/:id
DELETE /students/:id// Request body (JSON)
{
"name": "Grace Teo",
"course": "DIT",
"email": "grace.teo@sp.edu.sg"
}
// Response
{
"status": "created",
"id": 42,
"apiVersion": "v1"
}// Response
[
{
"id": 1,
"name": "Grace Teo",
"course": "DIT",
"email": "grace.teo@sp.edu.sg"
},
{
"id": 2,
"name": "Ahmad Malik",
"course": "DISM",
"email": "ahmad.malik@sp.edu.sg"
}
]GET /api/v1/students
1. Try: Read Replica (getReadDbConnection)
2. If error (timeout / network / replica down):
- Log error
- Fallback: getDbConnection() → Primary
3. Return data to UI (with 200 OK)
4. Optionally include headers:
X-DB-Source: replica | primary
X-API-Version: v1import express from "express";
import AWS from "aws-sdk";
import mysql from "mysql2/promise";
const app = express();
app.use(express.json());
const sm = new AWS.SecretsManager({ region: "ap-southeast-1" });
async function getDbConnection() {
const secret = await sm.getSecretValue({
SecretId: "studentapp/db/credentials"
}).promise();
const creds = JSON.parse(secret.SecretString);
return mysql.createConnection({
host: creds.host,
user: creds.username,
password: creds.password,
database: creds.dbname
});
}
async function getReadDbConnection() {
const secret = await sm.getSecretValue({
SecretId: "studentapp/db/credentials-readreplica"
}).promise();
const creds = JSON.parse(secret.SecretString);
return mysql.createConnection({
host: creds.host, // read replica endpoint
user: creds.username,
password: creds.password,
database: creds.dbname
});
}// Create (Primary)
app.post("/api/v1/students", async (req, res) => {
const conn = await getDbConnection();
const { name, course, email } = req.body;
await conn.execute(
"INSERT INTO students (name, course, email) VALUES (?, ?, ?)",
[name, course, email]
);
res.json({ status: "created", apiVersion: "v1" });
});
// Read (Read Replica with failover)
app.get("/api/v1/students", async (req, res) => {
try {
const conn = await getReadDbConnection();
const [rows] = await conn.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "replica");
res.setHeader("X-API-Version", "v1");
return res.json(rows);
} catch (err) {
console.error("Replica error:", err.message);
console.log("Failing over to PRIMARY…");
const primary = await getDbConnection();
const [rows2] = await primary.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "primary");
res.setHeader("X-API-Version", "v1");
return res.json(rows2);
}
});
// Update (Primary)
app.put("/api/v1/students/:id", async (req, res) => {
const conn = await getDbConnection();
const { name, course, email } = req.body;
await conn.execute(
"UPDATE students SET name=?, course=?, email=? WHERE id=?",
[name, course, email, req.params.id]
);
res.json({ status: "updated", apiVersion: "v1" });
});
// Delete (Primary)
app.delete("/api/v1/students/:id", async (req, res) => {
const conn = await getDbConnection();
await conn.execute("DELETE FROM students WHERE id=?", [req.params.id]);
res.json({ status: "deleted", apiVersion: "v1" });
});A simple single-page UI that talks to the /api/v1/students endpoints.
// On initial load
useEffect(() => {
fetch("/api/v1/students").then(...);
}, []);
// On search
// (Optional: debounce)
fetch("/api/v1/students?search=grace");
// On add
fetch("/api/v1/students", { method:"POST", body: JSON.stringify(newStudent) });
// On edit
fetch("/api/v1/students/42", { method:"PUT", body: JSON.stringify(updated) });
// On delete
fetch("/api/v1/students/42", { method:"DELETE" });| Name | Course | Actions | |
|---|---|---|---|
| Grace Teo | DIT | grace.teo@sp.edu.sg | ✏️🗑 |
| Ahmad Malik | DISM | ahmad.malik@sp.edu.sg | ✏️🗑 |
Are you sure you want to delete Grace Teo? This action cannot be undone.
{
"username": "admin",
"password": "SuperSecret123",
"host": "student-db.cluster-xxxx.ap-southeast-1.rds.amazonaws.com",
"dbname": "studentdb"
}{
"username": "readonly",
"password": "ReplicaUserPass789",
"host": "studentdb-replica.cluster-ro-xxxx.ap-southeast-1.rds.amazonaws.com",
"dbname": "studentdb"
}Simple schema for the students table with a GPA column and basic constraints.
CREATE DATABASE studentdb;
USE studentdb;
-- Students table
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
course VARCHAR(50) NOT NULL,
email VARCHAR(120) UNIQUE NOT NULL,
gpa DECIMAL(3,2) DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Use this to instantly populate the table for live demos.
INSERT INTO students (name, course, email, gpa) VALUES
('Grace Teo', 'DIT', 'grace.teo@sp.edu.sg', 3.85),
('Ahmad Malik', 'DISM', 'ahmad.malik@sp.edu.sg', 3.42),
('Nur Shafiqah', 'DIT', 'shafiqah.nur@sp.edu.sg', 3.67),
('Jerome Tan', 'DCITP', 'jerome.tan@sp.edu.sg', 2.91),
('Liu Wenjie', 'DIT', 'wenjie.liu@sp.edu.sg', 3.22),
('Chloe Wong', 'DSCA', 'chloe.wong@sp.edu.sg', 3.95),
('Rajesh Kumar', 'DISM', 'rajesh.kumar@sp.edu.sg', 2.80),
('Mei Ling', 'DCITP', 'meiling@sp.edu.sg', 3.15),
('Samuel Ng', 'DIT', 'samuel.ng@sp.edu.sg', 3.66),
('Fatimah Zahara', 'DISM', 'fatimah.zahara@sp.edu.sg', 3.78);Normalises course codes into a separate courses table.
CREATE TABLE courses (
code VARCHAR(10) PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
INSERT INTO courses VALUES
('DIT', 'Diploma in Information Technology'),
('DISM', 'Diploma in Infocomm Security Management'),
('DCITP','Common ICT Programme'),
('DSCA', 'Diploma in Data Science & AI');
ALTER TABLE students
ADD CONSTRAINT fk_course
FOREIGN KEY (course) REFERENCES courses(code);DELETE on a course code fails if students still reference it.ON DELETE RESTRICT vs CASCADE).Scales reads by sending GET /api/v1/students to a Read Replica instead of the primary.
getReadDbConnection()// Use Read Replica for GET
app.get("/api/v1/students", async (req, res) => {
const conn = await getReadDbConnection();
const [rows] = await conn.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "replica");
res.setHeader("X-API-Version", "v1");
res.json(rows);
});try {
const conn = await getReadDbConnection();
const [rows] = await conn.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "replica");
res.setHeader("X-API-Version", "v1");
res.json(rows);
} catch (err) {
const primary = await getDbConnection();
const [rows] = await primary.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "primary");
res.setHeader("X-API-Version", "v1");
res.json(rows);
}Use the buttons to animate the architecture and log what happens.
// UI → API v1
await fetch("/api/v1/students", { method: "GET" });// GET using Read Replica with failover (v1)
app.get("/api/v1/students", async (req, res) => {
try {
const conn = await getReadDbConnection();
const [rows] = await conn.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "replica");
res.setHeader("X-API-Version", "v1");
res.json(rows);
} catch (err) {
const primary = await getDbConnection();
const [rows] = await primary.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "primary");
res.setHeader("X-API-Version", "v1");
res.json(rows);
}
});// Secrets for primary and read replica
{
"primary": {
"username": "admin",
"password": "SuperSecret123",
"host": "student-db.cluster-xxxx.rds.amazonaws.com",
"dbname": "studentdb"
},
"replica": {
"username": "readonly",
"password": "ReplicaUserPass789",
"host": "studentdb-replica.cluster-ro-xxxx.rds.amazonaws.com",
"dbname": "studentdb"
}
}// Writes go to PRIMARY (v1)
await conn.execute(
"INSERT INTO students (name, course) VALUES (?, ?)",
["Alice", "DIT"]
);// Reads use Read Replica (v1)
async function getReadDbConnection() {
const s = await sm.getSecretValue({
SecretId: "studentapp/db/credentials-readreplica"
}).promise();
const creds = JSON.parse(s.SecretString);
return mysql.createConnection({
host: creds.host,
user: creds.username,
password: creds.password,
database: creds.dbname
});
}// UI refreshes from API v1 result
loadStudentsTable();// Failover logic (conceptual, v1)
try {
const conn = await getReadDbConnection();
const [rows] = await conn.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "replica");
res.setHeader("X-API-Version", "v1");
res.json(rows);
} catch (err) {
console.error("Replica down, switching to primary");
const primary = await getDbConnection();
const [rows2] = await primary.execute("SELECT * FROM students");
res.setHeader("X-DB-Source", "primary");
res.setHeader("X-API-Version", "v1");
res.json(rows2);
}// Conceptual replication lag (v1)
// 1) Write on primary
INSERT INTO students VALUES (...);
// 2) Replica applies change later
setTimeout(() => {
replica.applyChange(...);
}, 2000);
// GET immediately -> stale
// GET after 2s -> correct10 questions to test understanding of secure cloud database operations.
You have not submitted yet.
getDbConnection & getReadDbConnection.